#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import sys
import time
import string
import xlrd
import xlwt
allColleague = [ "陈方" ,"陈凌云" ,"陈雪" ,"邓伟成" ,"方达冲" ,"冯天良" ,"侯定" ,"胡传忠" ,"胡文龙" ,"金鑫" ,"孔令强" ,"李利" ,"林金钊" ,"刘凯" ,"刘旭东" ,"刘绪森" ,"李照" ,"李智" ,"卢炳全" ,"马冬梅" ,"莫会富" ,"莫锡正" ,"潘杨帆" ,"桑中海" ,"苏芦笛" ,"苏远腾" ,"唐纯杰" ,"唐立刚" ,"王辉" ,"王勇" ,"王远亮" ,"吴爱军" ,"肖方旭","肖锡祥" ,"杨进宁" ,"阳文宾" ,"杨章" ,"叶振华" ,"曾国智" ,"曾海龙" ,"曾庆明" ,"张飞" ,"张飞龙" ,"郑强" ,"邹勇军" ,"卢运康","胡雪情","何宁","罗永音","吴智敏","石彦华","张超","陈婉倩","吴再稳","龚柳青","陈飞","田超","徐日磊","邹加利","陈匡斐","吴勇","杨珍妮","左声东","刘少玲","魏伦","王中宜","李广","赵凤","李娜","刘云玉","臾奔奔","李军群","吴全"];

filterColumn=[0,1,6,8,9,10,14,15,17,23,30]
prefAllCollumn=[0,1,2,3,4,5,6,7,8,9,10]
columnAccepterNo=23

settingTeam=[ '莫会富',"马冬梅" ,"吴爱军","肖锡祥" ,"叶振华","邓伟成" ,"刘云玉","庾犇犇","李军群","吴全"]
commTeam=["陈凌云" ,"刘凯" ,"刘绪森" ,"曾海龙" ,"邹勇军","刘少玲","魏伦","王中宜","李广","赵凤","李娜"]
driverTeam=["刘旭东" ,"唐纯杰" ,"王辉" ,"王远亮" ,"杨进宁" ,"阳文宾" ,"曾国智" ,"曾庆明" ,"郑强"]
systemTeam=["胡文龙" ,"孔令强" ,"卢炳全" ,"莫锡正" ,"苏远腾" ,"唐立刚" ,"王勇" ,"肖方旭" ]
appTeam=["陈雪" ,"方达冲" ,"李利" ,"李照" ,"桑中海" ,"杨章" ,"张飞龙","卢运康","胡雪情","何宁","罗永音","吴智敏" ]
mmediaTeam=["陈方" ,"冯天良" ,"侯定" ,"胡传忠" ,"金鑫" ,"李智" ,"潘杨帆" ,"苏芦笛","石彦华","张超","陈婉倩","吴再稳","龚柳青","陈飞","田超"]
networkTeam=["张飞" ,"徐日磊","邹加利","陈匡斐","吴勇","杨珍妮","左声东"]

def Usage():
	print '''fs.mainsoft-filter.py -n your_all_new_bugs_xls_file [ -p your_all_preview_bug_xls_file] [ -o filter_output_xls_file]'''
	print '''fs.mainsoft-filter.py --new your_all_new_bugs_xls_file [ --preview your_all_preview_bug_xls_file] [ --output filter_output_xls_file]'''
	exit("test return form python");

def FilterAndTrace(pref,inf,outf):

	outWorkbook=xlwt.Workbook(encoding = 'utf-8');
	FilterBugXlsFile(inf,outWorkbook,outf)
	if pref != '':
		TraceBugStutus(pref,inf,outWorkbook)
	print "all finished, see at %s"%outf;
	outWorkbook.save(outf);
	

def FilterBugXlsFile(inf,outWorkbook,outf):
	inWorkbook=xlrd.open_workbook(inf);
	inSheet=inWorkbook.sheet_by_index(0);
	FilterAllBug(inSheet,outWorkbook,'allbugs');
	FilterByTeam(inSheet,outWorkbook,'setting',settingTeam);
	FilterByTeam(inSheet,outWorkbook,'comm',commTeam);
	FilterByTeam(inSheet,outWorkbook,'driver',driverTeam);
	FilterByTeam(inSheet,outWorkbook,'system',systemTeam);
	FilterByTeam(inSheet,outWorkbook,'app',appTeam);
	FilterByTeam(inSheet,outWorkbook,'mmedia',mmediaTeam);
	FilterByTeam(inSheet,outWorkbook,'network',networkTeam);
	outWorkbook.save(outf);
	


def TraceBugStutus(pref,inf,outWorkbook):
	prefWbRd=xlrd.open_workbook(pref);
	prefShRd=prefWbRd.sheet_by_name('allbugs');
	inffWbRd=xlrd.open_workbook(inf)
	infShRd=inffWbRd.sheet_by_index(0)
	newfShWt=outWorkbook.add_sheet('tracebug');
	WriteSpecifcRow(newfShWt,0,infShRd.row_values(0),filterColumn);
	for nRow in range(1,prefShRd.nrows):
		WriteSpecifcRow(newfShWt,nRow,prefShRd.row_values(nRow),prefAllCollumn)
		preBugId=GetBugId(prefShRd,nRow,1)
		result=SearchBugById(infShRd,preBugId)
		if result != None:
			newfShWt.write(nRow,11,result[0])
			newfShWt.write(nRow,12,result[1])
	

def GetBugId(sheet,nrow,ncol):
	return sheet.row_values(nrow)[ncol].split('[')[1].split(']')[0]

def SearchBugById(sheet,id):
	for currRow in range(1,sheet.nrows):
		if id == GetBugId(sheet,currRow,1):
			return (sheet.row_values(currRow)[6],sheet.row_values(currRow)[22])
	return None
def FilterByTeam(inSh,outWb,tName,tMem):
	curOutRow=1;
	outSh=outWb.add_sheet(tName,cell_overwrite_ok=True)
	WriteSpecifcRow(outSh,0,inSh.row_values(0),filterColumn);
	for curInRow in range(1,inSh.nrows):
		currInRowValue=FilterRow(inSh,curInRow,columnAccepterNo,tMem);
		if currInRowValue != None :
			WriteSpecifcRow(outSh,curOutRow,currInRowValue,filterColumn);
			curOutRow +=1;
			
def FilterAllBug(inSh,outWb,tName):
	curOutRow=1;
	outSh=outWb.add_sheet(tName,cell_overwrite_ok=True)
	WriteSpecifcRow(outSh,0,inSh.row_values(0),filterColumn);
	for curInRow in range(1,inSh.nrows):
		currInRowValue=inSh.row_values(curInRow);
		WriteSpecifcRow(outSh,curOutRow,currInRowValue,filterColumn);
		curOutRow +=1;


def FilterRow(sheet,rowNum,colNum,member):
	flag=False
	for name in member:
		if sheet.row_values(rowNum)[colNum].encode('utf-8') == name :
			return sheet.row_values(rowNum);
	return None;
def WriteSpecifcRow(sheet,rowNum,rowValue,whatRow):
	i=0;
	for c in whatRow:
		sheet.write(rowNum,i,rowValue[c]);
		i +=1;


def main(argv):
	inputXlsFile = ''
	outputXlsFile= ''
	inputXlsWorkbook=''
	outputXlsWorkbook=''
	preXlsFile=''
	arg=''
	i=1;
	if ( len(sys.argv) < 3 ):
		print "missing arguments !!!"
		Usage();
	while i<len(argv) and  ( ( len(argv) -1 ) % 2 ) == 0 :
		argOpt = argv[i]
		try:
			argValue = argv[i+1]
		except ValueError:
			argValue= ''
			Usage()
		if argOpt == '-n' or argOpt == '--new':
			inputXlsFile=argValue
			i += 2;
		elif argOpt == '-p' or argOpt == '--preview':
			preXlsFile=argValue
			i += 2;
		elif argOpt == '-o' or argOpt == '--output':
			outputXlsFile=argValue
			i += 2;
		else:
			print "Unknow argument !!"
			Usage();
	if inputXlsFile == '':
		print "Miss xls file !!!"
		Usage();
	if outputXlsFile == '':
		outname=time.strftime('%Y-%m-%d-%H:%M-bugs.xls',time.localtime(time.time()));
		outputXlsFile='%s'%(outname)
	FilterAndTrace(preXlsFile,inputXlsFile,outputXlsFile)
	

if __name__ == "__main__":
	main(sys.argv)
